"COPY" Statement
am 13.04.2010 09:42:19 von Renato Oliveira
--_000_7965A9DCF12CC14984420BCC37B1608F25ABF318EDElzargrantc ou_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Dear all,
I just had a thought, another one. Please the guys who have the powerful kn=
owledge I need your help ;-)
Let's suppose
I have Three DB servers ,physical servers - let's call them:
* Original_DB01
* New_DB02
* Third_DB03
1 - Let's suppose I have a PostgreSQL on 'Original_DB01' server with a Data=
Base of 162GB in size.
2 - Let's suppose I buy a new server 'New_DB02' - all singing all dancing c=
ool server, RAID10, 25GB RAM, 64Bit etc.
3 - Let's suppose I backup the old server with pg_dump to a file "pg_dump_o=
riginal_DB01"
4 - Let's also suppose I backup the schema only from 'Original_DB01'and res=
tore it to 'Third_DB03'.
5 - Straight after I backup the schema, I stop PostgreSQL on 'Original_DB01=
'.
6 - Then I restore the schema to 'Third_DB03' and point the application to =
it.
7 - Let's suppose the application can create tables and can start using the=
database on 'Third_DB03' and adding new data.
8 - Let's suppose I restore the FULL pg_dump file "pg_dump_original_DB01" =
to 'New_DB02'
9 - Once the pg_dump has been restored to 'New_DB02' I can then point the a=
pplication to it and data will be added to it straight away.
Question:
10 - Can I use 'COPY' statement to transfer the data from 'Third_DB03' to '=
New_DB02' without 'WIPING' or Deleting the existing data?
I want to merge the DATA from 'Third_DB03' to 'New_DB02' without deleting t=
he Database or the existing data on the existing database.
Let me explain the Idea behind this craziness.
I am using 'Third_DB03' because the amount of data to be transferred from i=
t, will be minimum, and easy to and quick to 'COPY'
This application has two part components (Data and Alarms)
We can get the data back in a very hard way, but without the data being in =
the database we can't generate alarms.
I would very much appreciate all your help on this idea, please.
I would be very glad and grateful for any comments and improvements on this=
idea, picking the holes etc.
Thank you very much in advance
Renato
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk
Grant Instruments (Cambridge) Ltd
Company registered in England, registration number 658133
Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is conf=
idential. It is intended only for the named recipients(s). If you are not t=
he named recipient please notify the sender immediately and do not disclose=
the contents to another person or take copies.
VIRUSES: The contents of this e-mail or attachment(s) may contain viruses w=
hich could damage your own computer system. Whilst Grant Instruments (Cambr=
idge) Ltd has taken every reasonable precaution to minimise this risk, we c=
annot accept liability for any damage which you sustain as a result of soft=
ware viruses. You should therefore carry out your own virus checks before o=
pening the attachment(s).
OpenXML: For information about the OpenXML file format in use within Grant =
Instruments please visit our website
..html>
--_000_7965A9DCF12CC14984420BCC37B1608F25ABF318EDElzargrantc ou_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
icrosoft.com/office/2004/12/omml" xmlns:o=3D"urn:schemas-microsoft-com:offi=
ce:office" xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:w=3D"urn:schemas=
-microsoft-com:office:word">
ascii" http-equiv=3D"Content-Type">
>
Dear all,
I just had a thought, another one. Please the guys who=
have
the powerful knowledge I need your help ;-)
Let’s suppose
I have Three DB servers ,physical servers –=
; let’s
call them:
lfo1'>
=3D'mso-list:Ignore'>·&=
nbsp;
Original_DB01
lfo1'>
=3D'mso-list:Ignore'>·&=
nbsp;
New_DB02
lfo1'>
=3D'mso-list:Ignore'>·&=
nbsp;
Third_DB03
1 – Let’s suppose I have a PostgreSQL on &=
#8216;Original_DB01’
server with a Data Base of 162GB in size.
2 – Let’s suppose I buy a new server ̵=
6;New_DB02’
- all singing all dancing cool server, RAID10, 25GB RAM, 64Bit etc.
:p>
3 – Let’s suppose I backup the old server =
with
pg_dump to a file “pg_dump_original_DB01”
4 – Let’s also suppose I backup the schema=
only
from ‘Original_DB01’and restore it to ‘Third_DB03’.=
5 – Straight after I backup the schema, I stop P=
ostgreSQL
on ‘Original_DB01’.
6 – Then I restore the schema to ‘Third_DB=
03’
and point the application to it.
7 – Let’s suppose the application can crea=
te
tables and can start using the database on ‘Third_DB03’ and add=
ing
new data.
8 – Let’s suppose I restore the FULL =
;pg_dump
file “pg_dump_original_DB01” to ‘New_DB02’
p>
9 – Once the pg_dump has been restored to ‘=
;New_DB02’
I can then point the application to it and data will be added to it straigh=
t
away.
Question:
10 – Can I use ‘COPY’ statement to
transfer the data from ‘Third_DB03’ to ‘New_DB02’ w=
ithout
‘WIPING’ or Deleting the existing data?
I want to merge the DATA from ‘Third_DB03’=
to ‘New_DB02’
without deleting the Database or the existing data on the existing database=
..
Let me explain the Idea behind this craziness.
:p>
I am using ‘Third_DB03’ because the amount=
of
data to be transferred from it, will be minimum, and easy to and quick to &=
#8216;COPY’
This application has two part components (Data and Ala=
rms)
We can get the data back in a very hard way, but witho=
ut the
data being in the database we can’t generate alarms.
I would very much appreciate all your help on this ide=
a,
please.
I would be very glad and grateful for any comments and
improvements on this idea, picking the holes etc.
Thank you very much in advance
Renato
Renato=
Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
FONT>
>
>
Tel: +=
44 (0)1763 260811
Fax: +44 (0)1763 262410
..co.uk/">www.grant.co.uk
>
Grant =
Instruments (Cambridge) Ltd
Company registered in England, re=
gistration number 658133
Registered office address:
29 Stat=
ion Road,
Shepreth,
CAMBS SG8 6GB
UK
>
ONT>
>
>
>
>
; COLOR: green; FONT-FAMILY: Webdings">
; COLOR: green; FONT-FAMILY: Webdings">P
=3D"EN-US" STYLE=3D"FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Verdana','=
sans-serif'">
OLOR: green; FONT-FAMILY: 'Arial','sans-serif'">Please consider the environ=
ment before printing this email
CONFIDENTIALITY: The =
information in this e-mail and any attachments is confidential. It is inten=
ded only for the named recipients(s). If you are not the named recipient pl=
ease notify the sender immediately and do not disclose the contents to anot=
her person or take copies.
VIRUSES: The contents=
of this e-mail or attachment(s) may contain viruses which could damage you=
r own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken e=
very reasonable precaution to minimise this risk, we cannot accept liabilit=
y for any damage which you sustain as a result of software viruses. You sho=
uld therefore carry out your own virus checks before opening the attachment=
(s).
OpenXML: For informat=
ion about the OpenXML file format in use within Grant Instruments please vi=
sit our =
--_000_7965A9DCF12CC14984420BCC37B1608F25ABF318EDElzargrantc ou_--
Re: "COPY" Statement
am 13.04.2010 09:55:54 von imartinez
--=-JZw98idRUCKZ4xHbFg7j
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
I think it depends of your datamodel and specifically of your table
constraints.
For example, if you have a table with a pk generated by a sequence, you
will have to deal with it before importing into your New_DB02. But for a
plain log table without constrains you can dump it an import into your
"just loaded" New_DB02 without any problem.=20
-----Original Message-----
From: Renato Oliveira
To: pgsql-admin@postgresql.org
Cc: Steve Fisher
Subject: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:42:19 +0100
Dear all,
=20
I just had a thought, another one. Please the guys who have the powerful
knowledge I need your help ;-)
=20
Letâ=99s suppose
I have Three DB servers ,physical servers â=93 letâ=99s call t=
hem:=20
· Original_DB01
· New_DB02
· Third_DB03
=20
1 â=93 Letâ=99s suppose I have a PostgreSQL on â=98Origina=
l_DB01â=99 server with a
Data Base of 162GB in size.
2 â=93 Letâ=99s suppose I buy a new server â=98New_DB02=E2=
- all singing all
dancing cool server, RAID10, 25GB RAM, 64Bit etc.
3 â=93 Letâ=99s suppose I backup the old server with pg_dump to=
a file
â=9Cpg_dump_original_DB01â=9D
4 â=93 Letâ=99s also suppose I backup the schema only from â=
=98Original_DB01â=99and
restore it to â=98Third_DB03â=99.
5 â=93 Straight after I backup the schema, I stop PostgreSQL on
â=98Original_DB01â=99.
6 â=93 Then I restore the schema to â=98Third_DB03â=99 and=
point the application
to it.
7 â=93 Letâ=99s suppose the application can create tables and c=
an start using
the database on â=98Third_DB03â=99 and adding new data.
8 â=93 Letâ=99s suppose I restore the FULL pg_dump file
â=9Cpg_dump_original_DB01â=9D to â=98New_DB02â=99
9 â=93 Once the pg_dump has been restored to â=98New_DB02â=
=99 I can then point
the application to it and data will be added to it straight away.
=20
Question:
10 â=93 Can I use â=98COPYâ=99 statement to transfer the d=
ata from â=98Third_DB03â=99
to â=98New_DB02â=99 without â=98WIPINGâ=99 or Deletin=
g the existing data?
I want to merge the DATA from â=98Third_DB03â=99 to â=98Ne=
w_DB02â=99 without
deleting the Database or the existing data on the existing database.
=20
Let me explain the Idea behind this craziness.
I am using â=98Third_DB03â=99 because the amount of data to be =
transferred
from it, will be minimum, and easy to and quick to â=98COPYâ=99
=20
This application has two part components (Data and Alarms)=20
We can get the data back in a very hard way, but without the data being
in the database we canâ=99t generate alarms.
=20
I would very much appreciate all your help on this idea, please.
I would be very glad and grateful for any comments and improvements on
this idea, picking the holes etc.
=20
Thank you very much in advance
=20
Renato
=20
=20
=20
=20
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
=20
Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk
=20
Grant Instruments (Cambridge) Ltd=20
=20
Company registered in England, registration number 658133
=20
Registered office address:
29 Station Road,=20
Shepreth,=20
CAMBS SG8 6GB=20
UK
=20
=20
=20
=20
=20
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you
are not the named recipient please notify the sender immediately and do
not disclose the contents to another person or take copies.=20
=20
VIRUSES: The contents of this e-mail or attachment(s) may contain
viruses which could damage your own computer system. Whilst Grant
Instruments (Cambridge) Ltd has taken every reasonable precaution to
minimise this risk, we cannot accept liability for any damage which you
sustain as a result of software viruses. You should therefore carry out
your own virus checks before opening the attachment(s).
=20
OpenXML: For information about the OpenXML file format in use within
Grant Instruments please visit our website
--=-JZw98idRUCKZ4xHbFg7j
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit
I think it depends of your datamodel and specifically of your table constraints.
For example, if you have a table with a pk generated by a sequence, you will have to deal with it before importing into your New_DB02. But for a plain log table without constrains you can dump it an import into your "just loaded" New_DB02 without any problem.
-----Original Message-----
From: Renato Oliveira <>
To: pgsql-admin@postgresql.org <>
Cc: Steve Fisher <>
Subject: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:42:19 +0100
Dear all,
I just had a thought, another one. Please the guys who have the powerful knowledge I need your help ;-)
Let’s suppose
I have Three DB servers ,physical servers – let’s call them:
· Original_DB01
· New_DB02
· Third_DB03
1 – Let’s suppose I have a PostgreSQL on ‘Original_DB01’ server with a Data Base of 162GB in size.
2 – Let’s suppose I buy a new server ‘New_DB02’ - all singing all dancing cool server, RAID10, 25GB RAM, 64Bit etc.
3 – Let’s suppose I backup the old server with pg_dump to a file “pg_dump_original_DB01”
4 – Let’s also suppose I backup the schema only from ‘Original_DB01’and restore it to ‘Third_DB03’.
5 – Straight after I backup the schema, I stop PostgreSQL on ‘Original_DB01’.
6 – Then I restore the schema to ‘Third_DB03’ and point the application to it.
7 – Let’s suppose the application can create tables and can start using the database on ‘Third_DB03’ and adding new data.
8 – Let’s suppose I restore the FULL pg_dump file “pg_dump_original_DB01” to ‘New_DB02’
9 – Once the pg_dump has been restored to ‘New_DB02’ I can then point the application to it and data will be added to it straight away.
Question:
10 – Can I use ‘COPY’ statement to transfer the data from ‘Third_DB03’ to ‘New_DB02’ without ‘WIPING’ or Deleting the existing data?
I want to merge the DATA from ‘Third_DB03’ to ‘New_DB02’ without deleting the Database or the existing data on the existing database.
Let me explain the Idea behind this craziness.
I am using ‘Third_DB03’ because the amount of data to be transferred from it, will be minimum, and easy to and quick to ‘COPY’
This application has two part components (Data and Alarms)
We can get the data back in a very hard way, but without the data being in the database we can’t generate alarms.
I would very much appreciate all your help on this idea, please.
I would be very glad and grateful for any comments and improvements on this idea, picking the holes etc.
Thank you very much in advance
Renato
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
Grant Instruments (Cambridge) Ltd
Company registered in England, registration number 658133
Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.
VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).
OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
--=-JZw98idRUCKZ4xHbFg7j--
Re: "COPY" Statement
am 13.04.2010 10:02:00 von imartinez
--=-tIQ7TCSJy17kCxzf/+FS
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Primary Key. :)
A Primary Key is always a constraint, since it cannot be duplicated. For
example, if you have a PK generated by a sequence starting at zero, when
you restore your third database into new server you will have duplicated
values in this column and COPY command will fail.
-----Original Message-----
From: Renato Oliveira
To: Iñigo Martinez Lasala
Subject: RE: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:59:34 +0100

Dear Iñigo,
=20
Thank you very much. What do you mean by â=98pkâ=99?
I am sorry.
=20
Thank you very much
=20
Renato
=20
=20
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
=20
Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk
=20
Grant Instruments (Cambridge) Ltd=20
=20
Company registered in England, registration number 658133
=20
Registered office address:
29 Station Road,=20
Shepreth,=20
CAMBS SG8 6GB=20
UK
=20
=20
From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]=20
Sent: 13 April 2010 08:56
To: Renato Oliveira
Cc: pgsql-admin@postgresql.org; Steve Fisher
Subject: Re: [ADMIN] 'COPY' Statement
=20
I think it depends of your datamodel and specifically of your table
constraints.
For example, if you have a table with a pk generated by a sequence, you
will have to deal with it before importing into your New_DB02. But for a
plain log table without constrains you can dump it an import into your
"just loaded" New_DB02 without any problem.=20
-----Original Message-----
From: Renato Oliveira
To: pgsql-admin@postgresql.org
Cc: Steve Fisher
Subject: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:42:19 +0100
Dear all,
=20
I just had a thought, another one. Please the guys who have the powerful
knowledge I need your help ;-)
=20
Letâ=99s suppose
I have Three DB servers ,physical servers â=93 letâ=99s call t=
hem:=20
· Original_DB01
· New_DB02
· Third_DB03
=20
1 â=93 Letâ=99s suppose I have a PostgreSQL on â=98Origina=
l_DB01â=99 server with a
Data Base of 162GB in size.
2 â=93 Letâ=99s suppose I buy a new server â=98New_DB02=E2=
- all singing all
dancing cool server, RAID10, 25GB RAM, 64Bit etc.
3 â=93 Letâ=99s suppose I backup the old server with pg_dump to=
a file
â=9Cpg_dump_original_DB01â=9D
4 â=93 Letâ=99s also suppose I backup the schema only from â=
=98Original_DB01â=99and
restore it to â=98Third_DB03â=99.
5 â=93 Straight after I backup the schema, I stop PostgreSQL on
â=98Original_DB01â=99.
6 â=93 Then I restore the schema to â=98Third_DB03â=99 and=
point the application
to it.
7 â=93 Letâ=99s suppose the application can create tables and c=
an start using
the database on â=98Third_DB03â=99 and adding new data.
8 â=93 Letâ=99s suppose I restore the FULL pg_dump file
â=9Cpg_dump_original_DB01â=9D to â=98New_DB02â=99
9 â=93 Once the pg_dump has been restored to â=98New_DB02â=
=99 I can then point
the application to it and data will be added to it straight away.
=20
Question:
10 â=93 Can I use â=98COPYâ=99 statement to transfer the d=
ata from â=98Third_DB03â=99
to â=98New_DB02â=99 without â=98WIPINGâ=99 or Deletin=
g the existing data?
I want to merge the DATA from â=98Third_DB03â=99 to â=98Ne=
w_DB02â=99 without
deleting the Database or the existing data on the existing database.
=20
Let me explain the Idea behind this craziness.
I am using â=98Third_DB03â=99 because the amount of data to be =
transferred
from it, will be minimum, and easy to and quick to â=98COPYâ=99
=20
This application has two part components (Data and Alarms)=20
We can get the data back in a very hard way, but without the data being
in the database we canâ=99t generate alarms.
=20
I would very much appreciate all your help on this idea, please.
I would be very glad and grateful for any comments and improvements on
this idea, picking the holes etc.
=20
Thank you very much in advance
=20
Renato
=20
=20
=20
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk Grant Instruments (Cambridge) Ltd=20
=20
Company registered in England, registration number 658133
=20
Registered office address:
29 Station Road,=20
Shepreth,=20
CAMBS SG8 6GB=20
UK =20
=20
=20
=20
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you
are not the named recipient please notify the sender immediately and do
not disclose the contents to another person or take copies. =20
VIRUSES: The contents of this e-mail or attachment(s) may contain
viruses which could damage your own computer system. Whilst Grant
Instruments (Cambridge) Ltd has taken every reasonable precaution to
minimise this risk, we cannot accept liability for any damage which you
sustain as a result of software viruses. You should therefore carry out
your own virus checks before opening the attachment(s). =20
OpenXML: For information about the OpenXML file format in use within
Grant Instruments please visit our website=20
=20
=20
=20
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you
are not the named recipient please notify the sender immediately and do
not disclose the contents to another person or take copies.=20
=20
VIRUSES: The contents of this e-mail or attachment(s) may contain
viruses which could damage your own computer system. Whilst Grant
Instruments (Cambridge) Ltd has taken every reasonable precaution to
minimise this risk, we cannot accept liability for any damage which you
sustain as a result of software viruses. You should therefore carry out
your own virus checks before opening the attachment(s).
=20
OpenXML: For information about the OpenXML file format in use within
Grant Instruments please visit our website
--=-tIQ7TCSJy17kCxzf/+FS
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit
Primary Key. :)
A Primary Key is always a constraint, since it cannot be duplicated. For example, if you have a PK generated by a sequence starting at zero, when you restore your third database into new server you will have duplicated values in this column and COPY command will fail.
-----Original Message-----
From: Renato Oliveira <>
To: Iñigo Martinez Lasala <>
Subject: RE: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:59:34 +0100
Dear Iñigo,
Thank you very much. What do you mean by ‘pk’?
I am sorry.
Thank you very much
Renato
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
Grant Instruments (Cambridge) Ltd
Company registered in England, registration number 658133
Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK
From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]
Sent: 13 April 2010 08:56
To: Renato Oliveira
Cc: pgsql-admin@postgresql.org; Steve Fisher
Subject: Re: [ADMIN] 'COPY' Statement
I think it depends of your datamodel and specifically of your table constraints.
For example, if you have a table with a pk generated by a sequence, you will have to deal with it before importing into your New_DB02. But for a plain log table without constrains you can dump it an import into your "just loaded" New_DB02 without any problem.
-----Original Message-----
From: Renato Oliveira <>
To: pgsql-admin@postgresql.org <>
Cc: Steve Fisher <>
Subject: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:42:19 +0100
Dear all,
I just had a thought, another one. Please the guys who have the powerful knowledge I need your help ;-)
Let’s suppose
I have Three DB servers ,physical servers – let’s call them:
· Original_DB01
· New_DB02
· Third_DB03
1 – Let’s suppose I have a PostgreSQL on ‘Original_DB01’ server with a Data Base of 162GB in size.
2 – Let’s suppose I buy a new server ‘New_DB02’ - all singing all dancing cool server, RAID10, 25GB RAM, 64Bit etc.
3 – Let’s suppose I backup the old server with pg_dump to a file “pg_dump_original_DB01”
4 – Let’s also suppose I backup the schema only from ‘Original_DB01’and restore it to ‘Third_DB03’.
5 – Straight after I backup the schema, I stop PostgreSQL on ‘Original_DB01’.
6 – Then I restore the schema to ‘Third_DB03’ and point the application to it.
7 – Let’s suppose the application can create tables and can start using the database on ‘Third_DB03’ and adding new data.
8 – Let’s suppose I restore the FULL pg_dump file “pg_dump_original_DB01” to ‘New_DB02’
9 – Once the pg_dump has been restored to ‘New_DB02’ I can then point the application to it and data will be added to it straight away.
Question:
10 – Can I use ‘COPY’ statement to transfer the data from ‘Third_DB03’ to ‘New_DB02’ without ‘WIPING’ or Deleting the existing data?
I want to merge the DATA from ‘Third_DB03’ to ‘New_DB02’ without deleting the Database or the existing data on the existing database.
Let me explain the Idea behind this craziness.
I am using ‘Third_DB03’ because the amount of data to be transferred from it, will be minimum, and easy to and quick to ‘COPY’
This application has two part components (Data and Alarms)
We can get the data back in a very hard way, but without the data being in the database we can’t generate alarms.
I would very much appreciate all your help on this idea, please.
I would be very glad and grateful for any comments and improvements on this idea, picking the holes etc.
Thank you very much in advance
Renato
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
Grant Instruments (Cambridge) Ltd
Company registered in England, registration number 658133
Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.
VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).
OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.
VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).
OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
--=-tIQ7TCSJy17kCxzf/+FS--
Re: "COPY" Statement
am 13.04.2010 10:48:35 von imartinez
--=-cwTuZAFb56F/84ZhxlcQ
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Then you will have no problem.
When using pg_dump do NOT include the "-c" flag (clean). For example:
pg_dump -U postgres -Fc -d DB_3 -f DB_3.dmp DB3
pg_restore -U postgres -Fc -d DB_2 DB3_dmp 2>error_import.log
1>import.log
Or if you prefer, use plan text (in order to check dump file with an
editor prior restoring):
pg_dump -U postgres -Fp -d DB_3 -f DB_3.dmp DB3
psql -U postgres -d DB_2 -f DB_3.dmp 2>error_import.log 1>import.log
You could alse use the "insert" way, but restore will take much longer.
-----Original Message-----
From: Renato Oliveira
To: Iñigo Martinez Lasala
Subject: RE: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 09:13:06 +0100

I see! Primary Key. Cool.
I think you can work out and change the primary key, thatâ=99s is th=
e
reason I am moving the application to third_server so we have very
little data to worry about.
=20
Thank you very much for all your help and comments.
=20
In resume I can â=98COPY all the data from third_server and use the =
same
COPY statement to migrate the data back to new_server?
=20
Thank you
=20
Renato
=20
=20
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
=20
Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk
=20
Grant Instruments (Cambridge) Ltd=20
=20
Company registered in England, registration number 658133
=20
Registered office address:
29 Station Road,=20
Shepreth,=20
CAMBS SG8 6GB=20
UK
=20
=20
From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]=20
Sent: 13 April 2010 09:02
To: Renato Oliveira
Cc: pgsql-admin
Subject: RE: [ADMIN] 'COPY' Statement
=20
Primary Key. :)
A Primary Key is always a constraint, since it cannot be duplicated. For
example, if you have a PK generated by a sequence starting at zero, when
you restore your third database into new server you will have duplicated
values in this column and COPY command will fail.
-----Original Message-----
From: Renato Oliveira
To: Iñigo Martinez Lasala
Subject: RE: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:59:34 +0100
ï»=BF Dear Iñigo,
=20
Thank you very much. What do you mean by â=98pkâ=99?
I am sorry.
=20
Thank you very much
=20
Renato
=20
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk Grant Instruments (Cambridge) Ltd=20
=20
Company registered in England, registration number 658133
=20
Registered office address:
29 Station Road,=20
Shepreth,=20
CAMBS SG8 6GB=20
UK =20
From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]
Sent:13 April 2010 08:56
To:Renato Oliveira
Cc:pgsql-admin@postgresql.org; Steve Fisher
Subject: Re: [ADMIN] 'COPY' Statement
=20
I think it depends of your datamodel and specifically of your table
constraints.
For example, if you have a table with a pk generated by a sequence, you
will have to deal with it before importing into your New_DB02. But for a
plain log table without constrains you can dump it an import into your
"just loaded" New_DB02 without any problem.=20
-----Original Message-----
From: Renato Oliveira
To: pgsql-admin@postgresql.org
Cc: Steve Fisher
Subject: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:42:19 +0100
Dear all,
=20
I just had a thought, another one. Please the guys who have the powerful
knowledge I need your help ;-)
=20
Letâ=99s suppose
I have Three DB servers ,physical servers â=93 letâ=99s call t=
hem:=20
· Original_DB01
· New_DB02
· Third_DB03
=20
1 â=93 Letâ=99s suppose I have a PostgreSQL on â=98Origina=
l_DB01â=99 server with a
Data Base of 162GB in size.
2 â=93 Letâ=99s suppose I buy a new server â=98New_DB02=E2=
- all singing all
dancing cool server, RAID10, 25GB RAM, 64Bit etc.
3 â=93 Letâ=99s suppose I backup the old server with pg_dump to=
a file
â=9Cpg_dump_original_DB01â=9D
4 â=93 Letâ=99s also suppose I backup the schema only from â=
=98Original_DB01â=99and
restore it to â=98Third_DB03â=99.
5 â=93 Straight after I backup the schema, I stop PostgreSQL on
â=98Original_DB01â=99.
6 â=93 Then I restore the schema to â=98Third_DB03â=99 and=
point the application
to it.
7 â=93 Letâ=99s suppose the application can create tables and c=
an start using
the database on â=98Third_DB03â=99 and adding new data.
8 â=93 Letâ=99s suppose I restore the FULL pg_dump file
â=9Cpg_dump_original_DB01â=9D to â=98New_DB02â=99
9 â=93 Once the pg_dump has been restored to â=98New_DB02â=
=99 I can then point
the application to it and data will be added to it straight away.
=20
Question:
10 â=93 Can I use â=98COPYâ=99 statement to transfer the d=
ata from â=98Third_DB03â=99
to â=98New_DB02â=99 without â=98WIPINGâ=99 or Deletin=
g the existing data?
I want to merge the DATA from â=98Third_DB03â=99 to â=98Ne=
w_DB02â=99 without
deleting the Database or the existing data on the existing database.
=20
Let me explain the Idea behind this craziness.
I am using â=98Third_DB03â=99 because the amount of data to be =
transferred
from it, will be minimum, and easy to and quick to â=98COPYâ=99
=20
This application has two part components (Data and Alarms)=20
We can get the data back in a very hard way, but without the data being
in the database we canâ=99t generate alarms.
=20
I would very much appreciate all your help on this idea, please.
I would be very glad and grateful for any comments and improvements on
this idea, picking the holes etc.
=20
Thank you very much in advance
=20
Renato
=20
=20
=20
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
www.grant.co.uk Grant Instruments (Cambridge) Ltd=20
=20
Company registered in England, registration number 658133
=20
Registered office address:
29 Station Road,=20
Shepreth,=20
CAMBS SG8 6GB=20
UK =20
=20
=20
=20
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you
are not the named recipient please notify the sender immediately and do
not disclose the contents to another person or take copies. =20
VIRUSES: The contents of this e-mail or attachment(s) may contain
viruses which could damage your own computer system. Whilst Grant
Instruments (Cambridge) Ltd has taken every reasonable precaution to
minimise this risk, we cannot accept liability for any damage which you
sustain as a result of software viruses. You should therefore carry out
your own virus checks before opening the attachment(s). =20
OpenXML: For information about the OpenXML file format in use within
Grant Instruments please visit our website=20
=20
=20
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you
are not the named recipient please notify the sender immediately and do
not disclose the contents to another person or take copies. =20
VIRUSES: The contents of this e-mail or attachment(s) may contain
viruses which could damage your own computer system. Whilst Grant
Instruments (Cambridge) Ltd has taken every reasonable precaution to
minimise this risk, we cannot accept liability for any damage which you
sustain as a result of software viruses. You should therefore carry out
your own virus checks before opening the attachment(s). =20
OpenXML: For information about the OpenXML file format in use within
Grant Instruments please visit our website=20
=20
=20
=20
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you
are not the named recipient please notify the sender immediately and do
not disclose the contents to another person or take copies.=20
=20
VIRUSES: The contents of this e-mail or attachment(s) may contain
viruses which could damage your own computer system. Whilst Grant
Instruments (Cambridge) Ltd has taken every reasonable precaution to
minimise this risk, we cannot accept liability for any damage which you
sustain as a result of software viruses. You should therefore carry out
your own virus checks before opening the attachment(s).
=20
OpenXML: For information about the OpenXML file format in use within
Grant Instruments please visit our website
--=-cwTuZAFb56F/84ZhxlcQ
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit
Then you will have no problem.
When using pg_dump do NOT include the "-c" flag (clean). For example:
pg_dump -U postgres -Fc -d DB_3 -f DB_3.dmp DB3
pg_restore -U postgres -Fc -d DB_2 DB3_dmp 2>error_import.log 1>import.log
Or if you prefer, use plan text (in order to check dump file with an editor prior restoring):
pg_dump -U postgres -Fp -d DB_3 -f DB_3.dmp DB3
psql -U postgres -d DB_2 -f DB_3.dmp 2>error_import.log 1>import.log
You could alse use the "insert" way, but restore will take much longer.
-----Original Message-----
From: Renato Oliveira <>
To: Iñigo Martinez Lasala <>
Subject: RE: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 09:13:06 +0100
I see! Primary Key. Cool.
I think you can work out and change the primary key, that’s is the reason I am moving the application to third_server so we have very little data to worry about.
Thank you very much for all your help and comments.
In resume I can ‘COPY all the data from third_server and use the same COPY statement to migrate the data back to new_server?
Thank you
Renato
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk
Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
Grant Instruments (Cambridge) Ltd
Company registered in England, registration number 658133
Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK
From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]
Sent: 13 April 2010 09:02
To: Renato Oliveira
Cc: pgsql-admin
Subject: RE: [ADMIN] 'COPY' Statement
Primary Key. :)
A Primary Key is always a constraint, since it cannot be duplicated. For example, if you have a PK generated by a sequence starting at zero, when you restore your third database into new server you will have duplicated values in this column and COPY command will fail.
-----Original Message-----
From: Renato Oliveira <>
To: Iñigo Martinez Lasala <>
Subject: RE: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:59:34 +0100
Dear Iñigo,
Thank you very much. What do you mean by ‘pk’?
I am sorry.
Thank you very much
Renato
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
Grant Instruments (Cambridge) Ltd
Company registered in England, registration number 658133
Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK
From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]
Sent:13 April 2010 08:56
To:Renato Oliveira
Cc:pgsql-admin@postgresql.org; Steve Fisher
Subject: Re: [ADMIN] 'COPY' Statement
I think it depends of your datamodel and specifically of your table constraints.
For example, if you have a table with a pk generated by a sequence, you will have to deal with it before importing into your New_DB02. But for a plain log table without constrains you can dump it an import into your "just loaded" New_DB02 without any problem.
-----Original Message-----
From: Renato Oliveira <>
To: pgsql-admin@postgresql.org <>
Cc: Steve Fisher <>
Subject: [ADMIN] 'COPY' Statement
Date: Tue, 13 Apr 2010 08:42:19 +0100
Dear all,
I just had a thought, another one. Please the guys who have the powerful knowledge I need your help ;-)
Let’s suppose
I have Three DB servers ,physical servers – let’s call them:
· Original_DB01
· New_DB02
· Third_DB03
1 – Let’s suppose I have a PostgreSQL on ‘Original_DB01’ server with a Data Base of 162GB in size.
2 – Let’s suppose I buy a new server ‘New_DB02’ - all singing all dancing cool server, RAID10, 25GB RAM, 64Bit etc.
3 – Let’s suppose I backup the old server with pg_dump to a file “pg_dump_original_DB01”
4 – Let’s also suppose I backup the schema only from ‘Original_DB01’and restore it to ‘Third_DB03’.
5 – Straight after I backup the schema, I stop PostgreSQL on ‘Original_DB01’.
6 – Then I restore the schema to ‘Third_DB03’ and point the application to it.
7 – Let’s suppose the application can create tables and can start using the database on ‘Third_DB03’ and adding new data.
8 – Let’s suppose I restore the FULL pg_dump file “pg_dump_original_DB01” to ‘New_DB02’
9 – Once the pg_dump has been restored to ‘New_DB02’ I can then point the application to it and data will be added to it straight away.
Question:
10 – Can I use ‘COPY’ statement to transfer the data from ‘Third_DB03’ to ‘New_DB02’ without ‘WIPING’ or Deleting the existing data?
I want to merge the DATA from ‘Third_DB03’ to ‘New_DB02’ without deleting the Database or the existing data on the existing database.
Let me explain the Idea behind this craziness.
I am using ‘Third_DB03’ because the amount of data to be transferred from it, will be minimum, and easy to and quick to ‘COPY’
This application has two part components (Data and Alarms)
We can get the data back in a very hard way, but without the data being in the database we can’t generate alarms.
I would very much appreciate all your help on this idea, please.
I would be very glad and grateful for any comments and improvements on this idea, picking the holes etc.
Thank you very much in advance
Renato
Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
Grant Instruments (Cambridge) Ltd
Company registered in England, registration number 658133
Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.
VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).
OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.
VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).
OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.
VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).
OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our
--=-cwTuZAFb56F/84ZhxlcQ--